/************************************************************************************/
/*  Program name CAPPOEXT_SUPPLIER_CONTACT.sql                                      */
/*  Creater:  Kenny, Leung (PCCW - UniHub)                                          */
/*************   History   *************************                                */
/* 20 Jan 2006, Kenny Leung         First created                                   */
/* 26 Jan 2006, Francis Lee         Modified
/************************************************************************************/

SET DOC OFF;      
SET VERIFY OFF;
SET SERVEROUTPUT ON SIZE 1000000;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPPOEXT_SUPPLIER_CONTACT';

	lv_Scc_Count	NUMBER	:=	0;
	lv_Err_Count	NUMBER	:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	lv_FileName		VARCHAR2(100)	:=	'CAPPO_SUPPLIER_CONTACT.dat';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;

	l_ngo_sob		VARCHAR2(100);
    l_sob_id		NUMBER;

	CURSOR Cur_F(P_SET_OF_BOOKS_ID NUMBER) IS
	SELECT PVC.* 
    FROM   PO_VENDOR_CONTACTS PVC,
           PO_VENDOR_SITES_ALL PVS,
           HR_ORGANIZATION_INFORMATION HOI
    WHERE  PVC.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID       
    AND    PVS.ORG_ID = HOI.ORGANIZATION_ID
    AND    HOI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' 
    AND    HOI.ORG_INFORMATION3 = TO_CHAR(P_SET_OF_BOOKS_ID);

BEGIN
    l_ngo_sob := '&1';
   
    begin
      select set_of_books_id
      into   l_sob_id
      from   gl_sets_of_books
      where  upper(short_name) = upper(l_ngo_sob);
    exception
      when others then
        dbms_output.put_line(l_ngo_sob||': set of books name not found');
    end;
   
    lv_UTL_File		:=	UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
        
	FOR C1 IN Cur_F(l_sob_id) LOOP
			
			UTL_FILE.PUT_LINE(
				      lv_UTL_File,
				      C1.VENDOR_CONTACT_ID       ||CHR(9)||
                      C1.LAST_UPDATE_DATE        ||CHR(9)||
                      C1.LAST_UPDATED_BY         ||CHR(9)||
                      C1.VENDOR_SITE_ID          ||CHR(9)||
                      C1.LAST_UPDATE_LOGIN       ||CHR(9)||
                      C1.CREATION_DATE           ||CHR(9)||
                      C1.CREATED_BY              ||CHR(9)||
                      C1.INACTIVE_DATE           ||CHR(9)||
                      C1.FIRST_NAME              ||CHR(9)||
                      C1.MIDDLE_NAME             ||CHR(9)||
                      C1.LAST_NAME               ||CHR(9)||
                      C1.PREFIX                  ||CHR(9)||
                      C1.TITLE                   ||CHR(9)||
                      C1.MAIL_STOP               ||CHR(9)||
                      C1.AREA_CODE               ||CHR(9)||
                      C1.PHONE                   ||CHR(9)||
                      C1.ATTRIBUTE_CATEGORY      ||CHR(9)||
                      C1.ATTRIBUTE1              ||CHR(9)||
                      C1.ATTRIBUTE2              ||CHR(9)||
                      C1.ATTRIBUTE3              ||CHR(9)||
                      C1.ATTRIBUTE4              ||CHR(9)||
                      C1.ATTRIBUTE5              ||CHR(9)||
                      C1.ATTRIBUTE6              ||CHR(9)||
                      C1.ATTRIBUTE7              ||CHR(9)||
                      C1.ATTRIBUTE8              ||CHR(9)||
                      C1.ATTRIBUTE9              ||CHR(9)||
                      C1.ATTRIBUTE10             ||CHR(9)||
                      C1.ATTRIBUTE11             ||CHR(9)||
                      C1.ATTRIBUTE12             ||CHR(9)||
                      C1.ATTRIBUTE13             ||CHR(9)||
                      C1.ATTRIBUTE14             ||CHR(9)||
                      C1.ATTRIBUTE15             ||CHR(9)||
                      C1.REQUEST_ID              ||CHR(9)||
                      C1.PROGRAM_APPLICATION_ID  ||CHR(9)||
                      C1.PROGRAM_ID              ||CHR(9)||
                      C1.PROGRAM_UPDATE_DATE     ||CHR(9)||
                      C1.CONTACT_NAME_ALT        ||CHR(9)||
                      C1.FIRST_NAME_ALT          ||CHR(9)||
                      C1.LAST_NAME_ALT           ||CHR(9)||
                      C1.DEPARTMENT              
			);

			lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;
		
	if lv_Scc_Count = 0 then   
      lv_Err_Count := lv_Err_Count + 1;
      lv_Err_Msg := l_ngo_sob || ': Supplier Contact not found';
    end if;
    
    -- insert into log table
    insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOEXT_SUPPLIER_CONTACT', sysdate, lv_Scc_Count, lv_Err_Count, lv_Scc_Count-lv_Err_Count, lv_Err_Msg );
  
    commit; 
    
    UTL_FILE.FClose(lv_UTL_File);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
END;
/

